package cn.schoolwow.quickdao.module.database.ddl.query.flow.entity;

import cn.schoolwow.quickdao.domain.database.parent.DatabaseType;
import cn.schoolwow.quickdao.domain.entity.Entity;
import cn.schoolwow.quickdao.module.database.parent.domain.ResultSetConsumer;
import cn.schoolwow.quickdao.module.database.parent.flow.executor.ExecuteQueryConnectionFlow;
import cn.schoolwow.quickflow.domain.FlowContext;
import cn.schoolwow.quickflow.flow.BusinessFlow;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GetEntityListFlow implements BusinessFlow {
    @Override
    public void executeBusinessFlow(FlowContext flowContext) throws Exception {
        DatabaseType databaseType = flowContext.checkInstanceData(DatabaseType.class);

        final List<Entity> entityList = new ArrayList<>();
        flowContext.putData("entityList", entityList);
        switch (databaseType){
            case H2:{
                getByH2(flowContext);
            }break;
            case SQLite:{
                getBySQLite(flowContext);
            }break;
            case Mysql:
            case MariaDB:{
                getByMysql(flowContext);
            }break;
            case Postgresql:{
                getByPostgres(flowContext);
            }break;
            case SQLServer:{
                getBySQLServer(flowContext);
            }break;
            case Oracle:{
                getByOracle(flowContext);
            }break;
        }
    }

    @Override
    public String name() {
        return "获取数据库表信息列表";
    }

    private void getByH2(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.getData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表列表")
                .putTemporaryData("sql", "show tables;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while (resultSet.next()) {
                            Entity entity = new Entity();
                            entity.tableName = resultSet.getString(1);
                            entityList.add(entity);
                        }
                    }
                })
                .execute();
    }

    private void getBySQLite(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.getData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表列表")
                .putTemporaryData("sql", "select name from sqlite_master where type='table' and name != 'sqlite_sequence';")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while (resultSet.next()) {
                            Entity entity = new Entity();
                            entity.tableName = resultSet.getString("name");
                            entityList.add(entity);
                        }
                    }
                })
                .execute();
    }

    private void getByMysql(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.getData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表列表")
                .putTemporaryData("sql", "show table status;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while (resultSet.next()) {
                            Entity entity = new Entity();
                            entity.tableName = resultSet.getString("name");
                            entity.comment = resultSet.getString("comment").replace("\"", "\\\"");
                            entityList.add(entity);
                        }
                    }
                })
                .execute();
    }

    private void getByPostgres(FlowContext flowContext) throws SQLException {
        List<Entity> entityList = (List<Entity>) flowContext.getData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表列表")
                .putTemporaryData("sql", "select relname as name,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where  relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while (resultSet.next()) {
                            Entity entity = new Entity();
                            entity.tableName = resultSet.getString("name");
                            entity.comment = resultSet.getString("comment");
                            entityList.add(entity);
                        }
                    }
                })
                .execute();
    }

    private void getBySQLServer(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.getData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表列表")
                .putTemporaryData("sql", "select name from sysobjects where xtype='u' order by name;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while (resultSet.next()) {
                            Entity entity = new Entity();
                            entity.tableName = resultSet.getString("name");
                            entityList.add(entity);
                        }
                    }
                })
                .execute();
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表注释")
                .putTemporaryData("sql", "select so.name table_name, isnull(convert(varchar(255),value),'') comment from sys.extended_properties ex_p left join sys.sysobjects so on ex_p.major_id = so.id where ex_p.minor_id=0;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while (resultSet.next()) {
                            String tableName = resultSet.getString("table_name");
                            for (Entity entity : entityList) {
                                if(entity.tableName.equalsIgnoreCase(tableName)){
                                    entity.comment = resultSet.getString("comment");
                                    break;
                                }
                            }
                        }
                    }
                })
                .execute();
    }

    private void getByOracle(FlowContext flowContext){
        List<Entity> entityList = (List<Entity>) flowContext.getData("entityList");
        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表列表")
                .putTemporaryData("sql", "select user_tables.table_name, user_tab_comments.comments from user_tables left join user_tab_comments on user_tables.table_name = user_tab_comments.table_name")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while (resultSet.next()) {
                            Entity entity = new Entity();
                            entity.tableName = resultSet.getString("table_name");
                            entity.comment = resultSet.getString("comments");
                            entityList.add(entity);
                        }
                    }
                })
                .execute();
    }
}
